Filling a List View control

The best_buy database

The exercises in this section require the best_buy database that is described in Wintempla > SQL > ODBC . If you do not have this database, you must create it before continuing with the problems.

Los ejercicios en esta sección requieren la base de datos de best_buy descrita en Wintempla > SQL > ODBC . Si usted no tiene esta base de datos, usted debe crearla antes de continuar con los problemas.

Problem 1

Create a Wintempla project called CategoryList to display the categories in a list view control (use a dialog application). Insert a list view control as shown; set the name of the control to lvCategory.

Step A

Edit the connection string in the stdafx.h file as shown below.

Step B

Drag the SELECT list view template from Microsoft Visual Studio menu: Tools > Add Wintempla Item... > Clipboard Code > SQL Application Programming > SELECT listView.

Step C

Edit the CategoryList.cpp file as shown.

CategoryList.cpp

void CategoryList::Window_Open(Win::Event& e)
{
     //________________________________________________________ lvCategory
     lvCategory.Cols.Add(0, LVCFMT_LEFT, 200, L"Category");
     Sql::SqlConnection conn;
     try
     {
          //conn.OpenSession(DSN, USERNAME, PASSWORD); //Control Panel>Administrative Tools>Data Sources (ODBC)>Create dsn_myDatabase
          conn.OpenSession(hWnd, CONNECTION_STRING);
          conn.ExecuteSelect(L"SELECT category_id, descr FROM category", 100, lvCategory);
     }
     catch (Sql::SqlException e)
     {
          this->MessageBox(e.GetDescription(), L"Error", MB_OK | MB_ICONERROR);
     }
}

Tip

The list view controls allows saving an integer value with each row (item) in the control, see figure. This value is not shown to the user. A programmer can store any value in the Data field of each item; however, most programmers store the primary key of the table. In the previous case, the list view control has only one column; thus, the category_id is stored but not shown. If the number of columns in the list view control is equal to the number of columns in the SQL statement, all the columns will be shown.

Problem 1a.

Repeat the previous problem using Win32, call your project CategoryList32. Note that the List View control is called List Control in the toolbox of Win32.

Repita el problema previo usando Win32, llame a su proyecto CategoryList32. Observe que el control de List View se llama List Control en las herramientas de Win32.

CategoryList32.cpp

//_________________________________________________ CategoryList32.cpp
#include "stdafx.h"
#include "CategoryList32.h"
#include <commctrl.h> // Advanced Controls
//#include <commdlg.h> // Standard Dialogs
#pragma comment(lib, "comctl32.lib")
using namespace std;
#include <sqlucode.h> // SQL
#define MAX_COLUMN_SIZE 100

INT_PTR Window_Open(HWND hWnd, WPARAM wParam, LPARAM lParam)
{
     ::SetWindowText(hWnd, L"CategoryList32");
     //_____________________________________________ Add Column To List View
     LVCOLUMN lvcol;
     lvcol.mask = LVCF_FMT | LVCF_WIDTH | LVCF_TEXT;
     lvcol.fmt = LVCFMT_LEFT;
     lvcol.cx = 200;
     lvcol.pszText = (wchar_t*)L"Category";
     ::SendMessage(::GetDlgItem(hWnd, ID_LV_CATEGORY),(UINT)LVM_INSERTCOLUMN, (WPARAM)(int)0, (LPARAM)(LPLVCOLUMN)&lvcol);

     //_____________________________________________ Variables for SQL
     HENV henv = SQL_NULL_HENV;
     HDBC hdbc = SQL_NULL_HDBC;
     HSTMT hstmt = SQL_NULL_HSTMT;
     bool connected = false;
     RETCODE retcode = SQL_SUCCESS;
     //_____________________________________________ Memory Allocation for SQL Environment
     retcode = ::SQLAllocEnv(&henv);
     if ((retcode == SQL_SUCCESS) || (retcode == SQL_SUCCESS_WITH_INFO))
     {
     }
     else
     {
          ::MessageBox(hWnd, L"SQLAllocEnv", L"Error", MB_OK | MB_ICONERROR);
          // Clean Up ...
     }
     //________________________________________________ Memory Allocation for SQL Connection
     retcode = ::SQLAllocConnect(henv, &hdbc);
     if ((retcode == SQL_SUCCESS) || (retcode == SQL_SUCCESS_WITH_INFO))
     {
     }
     else
     {
          ::MessageBox(hWnd, L"SQLAllocConnect", L"Error", MB_OK | MB_ICONERROR);
          // Clean Up ...
     }
     //_________________________________________________ Connect to Database
     wchar_t szOutConn[1024];
     SQLSMALLINT cbOutConn;
     wchar_t* connectionString = L"DRIVER={SQL Server};server=localhost\\SQLEXPRESS;database=best_buy;Trusted_Connection=yes";
     retcode = ::SQLDriverConnect(hdbc, hWnd, (SQLWCHAR*)connectionString, SQL_NTS, (SQLWCHAR*)szOutConn,
          1024, &cbOutConn, SQL_DRIVER_COMPLETE);
     connected = ((retcode == SQL_SUCCESS) || (retcode == SQL_SUCCESS_WITH_INFO));
     //________________________________________________ Memory Allocation for SQL Statement
     retcode = ::SQLAllocStmt(hdbc, &hstmt);
     if ((retcode == SQL_SUCCESS) || (retcode == SQL_SUCCESS_WITH_INFO))
     {
     }
     else
     {
          ::MessageBox(hWnd, L"SQLAllocStmt", L"Error", MB_OK | MB_ICONERROR);
          // Clean Up ...
     }
     //__________________________________________________ SQL Exect Direct
     retcode = ::SQLExecDirect(hstmt, (SQLWCHAR*)L"SELECT category_id, descr FROM category", SQL_NTS);
     if ((retcode == SQL_SUCCESS) || (retcode == SQL_SUCCESS_WITH_INFO))
     {
     }
     else
     {
          ::MessageBox(hWnd, L"SQLExecDirect", L"Error", MB_OK | MB_ICONERROR);
          // Clean Up ...
     }
     //___________________________________________________ Memory Allocation for Fetching
     const int numbColumns = 2;
     int i = 0;
     wchar_t** pData = new wchar_t*[numbColumns];

     for (i = 0; i<numbColumns; i++)
     {
          pData[i] = new wchar_t[MAX_COLUMN_SIZE];
          pData[i][0] = '\0';
     }
     //_____________________________________________________ Bind Columns
     SQLLEN bindColStr[numbColumns];
     for (i = 0; i<numbColumns; i++)
     {
          retcode = ::SQLBindCol(hstmt, i + 1, SQL_C_TCHAR, pData[i], MAX_COLUMN_SIZE, &bindColStr[i]);
          if ((retcode == SQL_SUCCESS) || (retcode == SQL_SUCCESS_WITH_INFO))
          {
          }
          else
          {
               ::MessageBox(hWnd, L"SQLBindCol", L"Error", MB_OK | MB_ICONERROR);
               // Clean Up ...
               break;
          }
     }
     //_______________________________________________________________ Fetch
     LPARAM userData;
     LVITEM lvi;
     ZeroMemory(&lvi, sizeof(LVITEM));
     int index = 0;
     while (true)
     {
          retcode = ::SQLFetch(hstmt);
          if ((retcode == SQL_SUCCESS) || (retcode == SQL_SUCCESS_WITH_INFO))
          {
               userData = (LPARAM)_wtoi(pData[0]);
               //_______________________________________ Insert List View Item
               lvi.pszText = (wchar_t*)pData[1];
               lvi.iItem = index;
               lvi.mask = LVIF_TEXT | LVIF_PARAM;// | LVIF_STATE ;
               lvi.lParam = userData;
               ::SendMessage(::GetDlgItem(hWnd, ID_LV_CATEGORY), (UINT)LVM_INSERTITEM, 0, (LPARAM)(LPLVITEM)&lvi);
               for (i = 0; i<numbColumns; i++) pData[i][0] = '\0';
               index++;
          }
          else
          {
               break;
          }
     }
     ::SQLCloseCursor(hstmt);
     if (pData != NULL)
     {
          for (i = 0; i<numbColumns; i++) if (pData[i]) delete[] pData[i];
          delete[] pData;
     }
     //_____________________________________________ SQL Clean Up
     if (hstmt != SQL_NULL_HSTMT) ::SQLFreeStmt(hstmt, SQL_DROP);
     if (connected && (hdbc != SQL_NULL_HDBC)) ::SQLDisconnect(hdbc);
     if (hdbc != SQL_NULL_HDBC) :: SQLFreeConnect(hdbc);
     if (henv != SQL_NULL_HENV) :: SQLFreeEnv(henv);
     return TRUE;
}

INT_PTR CALLBACK WndProc(HWND hWnd, UINT message, WPARAM wParam, LPARAM lParam)
{
     switch (message)
     {
     case WM_INITDIALOG:
          return Window_Open(hWnd, wParam, lParam);
     case WM_COMMAND:
          if (LOWORD(wParam) == IDCANCEL) ::EndDialog(hWnd, 0);
          break;
     }
     return (INT_PTR)FALSE;
}

int APIENTRY _tWinMain(HINSTANCE hInstance, HINSTANCE, LPTSTR cmdLine, int cmdShow)
{
     //__________________________________________________ Load Advanced Controls
     INITCOMMONCONTROLSEX init;
     init.dwSize = sizeof(INITCOMMONCONTROLSEX);
     init.dwICC = ICC_WIN95_CLASSES;
     ::InitCommonControlsEx(&init);
     //
     ::DialogBox(hInstance, MAKEINTRESOURCE(IDD_DIALOG1), NULL, WndProc);
     return 0;
}

ODBC in C#

To use an ODBC instead of a connection string to connect to a database using C# some commands must be replaced as shown in the following table and following code.

Connection String	ODBC
System.Data.SqlClient.SqlConnection	System.Data.Odbc.OdbcConnection
System.Data.SqlClient.SqlCommand	System.Data.Odbc.OdbcCommand
System.Data.SqlClient.SqlDataAdapter	System.Data.Odbc.OdbcDataAdapter
System.Data.SqlClient.SqlException	System.Data.Odbc.OdbcException

Program.cs

Problem 2

Repeat the last problem using C#, named your project CategoryListS. Create a C# Windows Form Application. Drag a Grid View list as shown. (a) Using a connection string. (b) Using ODBC.

Step A

In the event tab, double click the Load event to create the handler function.

Step B

Add the DatabaseInfo class to your project: PROJECT> Add Class... > C# class

DatabaseInfo.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ByCategoryS
{
     class DatabaseInfo
     {
          public static string GetConnectionInfo()
          {
               return "server=SELO\\SQLExpress;database=best_buy;Trusted_Connection=yes";
          }
     }
}

Step C

Edit the CategoryList.cs file as shown

CategoryList.cs

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient; // <<<<<<<<<<<< ADD THIS LINE

namespace CategoryListS
{
     public partial class Form1 : Form
     {
          public Form1()
          {
               InitializeComponent();
          }

          private void Form1_Load(object sender, EventArgs e)
          {
               Text = "CategoryListS";
               //__________________________________ Column setup
               this.listViewCategory.View = View.Details;
               this.listViewCategory.Columns.Add("Category", 200, HorizontalAlignment.Left);

               //__________________________________ Fill the list view
               SqlConnection conn = new SqlConnection(DatabaseInfo.GetConnectionInfo());
               string data;

               try
               {
                    conn.Open();
                    string sqlcmd = "SELECT category_id, descr FROM category";

                    DataSet dataSet = new DataSet();
                    SqlDataAdapter adapter = new SqlDataAdapter(sqlcmd, conn);
                    adapter.Fill(dataSet);

                    if (dataSet.Tables.Count == 0) return;
                    DataTable dataTable = dataSet.Tables[0];
                    if (dataTable.Rows.Count == 0) return;
                    foreach (DataRow dataRow in dataTable.Rows)
                    {
                         data = System.Convert.ToString((int)dataRow["category_id"]);
                         this.listViewCategory.Items.Add(data, (string)dataRow["descr"], 0);
                    }
               }
               catch (SqlException ex)
               {
                    MessageBox.Show(this, ex.Message, "Error");
                    this.Text = ex.Message;
               }
               finally
               {
                    conn.Close();
               }
          }
     }
}

Tip

Each item in a list view control has a data, a text, and an array of string for the sub-items. The sub-items are displayed as text in each column. In C#, it is necessary to create a ListViewItem when the item has sub-items as shown below.

Program.cs

ListViewItem lvItem = new ListViewItem();
lvItem.Tag = "My primary key";
lvItem.Text = "One";
lvItem.SubItems.Add("Second");

Tip

You may also use the DataGrid control in the Platform.NET to create a GUI. This control is designed to ease some of the common database operations. The Platform.NET offers several ways to manage data connection, such as the SqlDataReader and the SqlDataAdapter.

Problem 3

Create a Wintempla Web application called CategoryListWeb using C++. There are some differences between the list view control in a desktop application and a web application. First, to add a column in a desktop application you must provide the column index, while in a web application all the columns are added sequentially. Second, the SetRedraw command, used in desktop applications, does not exists in web applications as the controls are not redraw until they have been modified. (a) Using a connection string. (b) Using ODBC.

Step A

Add a list view control as shown and set the name of the control to lvCategory by pressing theList View button as shown. You may use the

to edit the web page. The HTML view is read only. However, each control has custom HTML than can be displayed before and after each control.

Step B

Edit the connection string in the stdafx.h file.

Step C

Edit the Index.cpp file as shown below.

Index.cpp

#include "stdafx.h" //_____________________________________________ Index.cpp
#include "Index.h"

void Index::Window_Open(Web::HttpConnector& h)
{
     //________________________________________________________ lvCategory
     lvCategory.Cols.Add(LVCFMT_LEFT, 30, L"Category");
     Sql::SqlConnection conn;
     try
     {
          //conn.OpenSession(DSN, USERNAME, PASSWORD); //Control Panel>Administrative Tools>Data Sources (ODBC)>Create dsn_myDatabase
          conn.OpenSession(NULL, CONNECTION_STRING);
          conn.ExecuteSelect(L"SELECT category_id, descr FROM category", 100, lvCategory);
     }
     catch (Sql::SqlException e)
     {
          this->MessageBox(e.GetDescription(), L"Error", MB_OK | MB_ICONERROR);
     }
}

Problem 4

Publish the web application called CategoryListWeb to a web server, see Wintempla > Publishing a Web Site > Public SQL Web site use Anonymous Access.

Publique la aplicación llamada CategoryListWeb en un servidor web, vea Wintempla > Publishing a Web Site > Public SQL Web site use Acceso Anónimo.

Tip

When converting code of list view control from a desktop application to a web application:

Remove the column index when adding columns to the control
As the column width in a desktop application is in pixels, while the column width in a web application is in letters: divide the column width by 10

Tip

Every time a web page is debugged in Wintempla project, a web server starts running. After that your default browser opens, you may open any other browser to to debug the page. When done debugging stop the web server by pressing the stop button in Microsoft Visual Studio, or stop (and close) the Web server.

Tip

If a web page is not found, when trying to open it you may replace the word localhost with 127.0.0.1 which is the loopback address or local host address.

Tip

You may try to run these applications using your smartphone, see the last part of Wintempla > Sockets > Introduction to learn how to create an Ad Hoc network or a Host Spot.

Problem 5

Create a ASP.NET Empty Web application called CategoryListWebS using C#. This problem requires the DatabaseInfo class previously created. Note that the namespace of the DatabaseInfo class must much with the project name.

Cree una aplicación ASP.NET vacía Web llamada CategoryListWebS usando C#. Este problema requiere la clase DatabaseInfo previamente creada. Note que el namespace de la clase DatabaseInfo debe coincidir con el nombre del proyecto.

Step A

From the menu: Project>Add New Item... .

Desde el menú: Project>Add New Item... .

DatabaseInfo.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace CategoryListWebS
{
     class DatabaseInfo
     {
          public static string GetConnectionInfo()
          {
               return "server=SELO\\SQLExpress;database=best_buy;Trusted_Connection=yes";
          }
     }
}

Step B

Change the view to Design Mode and insert a Grid View control as shown. Drag the control from the toolbox and drop it inside the GUI body.

Cambie la vista al Modo de Diseño e inserte un control de Grid View como se muestra. Arrastre el control desde la caja de herramientas y suéltelo dentro del cuerpo de la GUI.

Step C

Edit the index.aspx.cs file as shown.

index.aspx.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace CategoryListWebS
{
     public partial class index : System.Web.UI.Page
     {
          protected void Page_Load(object sender, EventArgs e)
          {
               System.Data.SqlClient.SqlConnection conn =
                    new System.Data.SqlClient.SqlConnection(DatabaseInfo.GetConnectionInfo());
               System.Data.SqlClient.SqlCommand cmd = null;

               try
               {
                    conn.Open();
                    cmd = new System.Data.SqlClient.SqlCommand("SELECT category_id, descr FROM category", conn);
                    System.Data.DataSet dataSet = new System.Data.DataSet();
                    System.Data.SqlClient.SqlDataAdapter daUsuario = new System.Data.SqlClient.SqlDataAdapter(cmd);
                    daUsuario.Fill(dataSet);
                    this.GridView1.DataSource = dataSet;
                    this.GridView1.AutoGenerateColumns = true;
                    this.GridView1.DataBind();
               }
               catch (System.Data.SqlClient.SqlException ex)
               {
                    //this.LabelError.Text = ex.Message;
               }
               finally
               {
                    conn.Close();
               }
          }
     }
}

Tip

When debugging a ASP.NET web page, be sure to stop the web server by making right click with the mouse in the development web server (see figure below) using the Stop option.

Cuando esté depurando una página de ASP.NET, asegúrese de detener el servidor web haciendo clic con el botón derecho del ratón en el servidor web de desarrollo (vea la figura debajo) usando la opción de Stop.

Problem 6

Create a Java application called CategoryListJ to display the list of categories. You must set the CLASSPATH for the specific JDBC driver. The MySQL driver for JDBC is installed when you install MySQL; be sure to check this option during a customized installation (C:\Program Files\My SQL\connector J 5.0).

CategoryListJ.java

import java.sql.*;

public class CategoryListJ
{
     public static void main( String[] args )
     {
          try
          {
               Class.forName("com.mysql.jdbc.Driver");
               String url = "jdbc:mysql://localhost/nombre_base_datos"; // Se puede usar un IP en lugar de localhost
               Connection con = DriverManager.getConnection(url, "my_username", "my_password");
               //
               Statement stmt = con.createStatement();
               ResultSet resultSet = stmt.executeQuery("SELECT name FROM client");

               while( resultSet.next() ) // Mientras se tengan clientes
               {
                    String data = resultSet.getString( "nombre" );
                    // Do something with data
                    System.out.println(data);
               }
               stmt.close();
          }
          catch( Exception e )
          {
               System.out.println(e.getMessage()); //Mostrar el error
          }
     }
}

Problem 7

Add a new Web IIS application to the CategoryList solution to create a Dual Application. A dual application is a Microsoft Visual Studio solution with two projects: a desktop application project and a Web application project. Review the instructions at: Wintempla > Introduction > Dual Calculator before working on this problem. Do not forget to edit the SQL connection string by editing the stdafx.h file in both projects.

CategoryList.cpp

...
void CategoryList::Window_Open(Win::Event& e)
{
     //________________________________________________________ lvCategory
     lvCategory.Cols.Add(0, LVCFMT_LEFT, 200, L"Category");
     CategoryListDual::Window_Open(*this, NULL);
}

Index.cpp

...
void Index::Window_Open(Web::HttpConnector& h)
{
     //________________________________________________________ lvCategory
     lvCategory.Cols.Add(LVCFMT_LEFT, 20, L"Category");
     CategoryListDual::Window_Open(*this, &h);
}

CategoryListDual.cpp

...
void CategoryListDual::Window_Open(Sys::IWindow& window, Web::HttpConnector* h)
{
     Sql::SqlConnection conn;
     try
     {
          //conn.OpenSession(DSN, USERNAME, PASSWORD); //Control Panel>Administrative Tools>Data Sources (ODBC)>Create dsn_myDatabase
          conn.OpenSession(window, CONNECTION_STRING);
          conn.ExecuteSelect(L"SELECT category_id, descr FROM category", 100, lvCategoryD);
     }
     catch (Sql::SqlException e)
     {
          window.MessageBox(e.GetDescription(), L"Error", MB_OK | MB_ICONERROR);
     }
}

Filling a List View control

The best_buy database

ODBC in C#

© Copyright 2000-2021 Wintempla selo. All Rights Reserved. Jul 22 2021. Home